package cn.stylefeng.guns.modular.tool.service.impl;

import java.io.IOException;
import java.io.InputStream;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;

import cn.stylefeng.guns.base.pojo.page.LayuiPageFactory;
import cn.stylefeng.guns.base.pojo.page.LayuiPageInfo;
import cn.stylefeng.guns.core.util.ExcelWrite;
import cn.stylefeng.guns.modular.tool.entity.ToolExpressReconciliationFw;
import cn.stylefeng.guns.modular.tool.mapper.ToolExpressReconciliationFwMapper;
import cn.stylefeng.guns.modular.tool.model.params.ToolExpressReconciliationFwInstallParam;
import cn.stylefeng.guns.modular.tool.model.params.ToolExpressReconciliationFwParam;
import cn.stylefeng.guns.modular.tool.model.params.ToolExpressReconciliationRfParam;
import cn.stylefeng.guns.modular.tool.model.params.ToolExpressReconciliationYfInstallParam;
import cn.stylefeng.guns.modular.tool.model.result.ToolExpressReconciliationFwInstallResult;
import cn.stylefeng.guns.modular.tool.model.result.ToolExpressReconciliationFwResult;
import cn.stylefeng.guns.modular.tool.model.result.ToolExpressReconciliationYfInstallResult;
import cn.stylefeng.guns.modular.tool.model.result.orderCount;
import cn.stylefeng.guns.modular.tool.service.ToolExpressReconciliationFwInstallService;
import  cn.stylefeng.guns.modular.tool.service.ToolExpressReconciliationFwService;
import cn.stylefeng.guns.modular.tool.service.ToolExpressReconciliationRfService;
import cn.stylefeng.guns.modular.tool.service.ToolExpressReconciliationYfInstallService;
import cn.stylefeng.guns.sys.modular.system.entity.User;
import cn.stylefeng.guns.sys.modular.system.service.UserService;
import cn.stylefeng.roses.core.reqres.response.ResponseData;
import cn.stylefeng.roses.core.util.ToolUtil;

/**
 * <p>
 * 丰网对账管理 服务实现类
 * </p>
 * @author zhucx
 * @since 2023-01-13
 */
@Service
public class ToolExpressReconciliationFwServiceImpl extends ServiceImpl<ToolExpressReconciliationFwMapper, ToolExpressReconciliationFw> implements ToolExpressReconciliationFwService {

    @Override
    public void add(ToolExpressReconciliationFwParam param){
        ToolExpressReconciliationFw entity = getEntity(param);
        this.save(entity);
    }

    @Override
    public void delete(ToolExpressReconciliationFwParam param){
        this.removeById(getKey(param));
    }

    @Override
    public void update(ToolExpressReconciliationFwParam param){
        ToolExpressReconciliationFw oldEntity = getOldEntity(param);
        ToolExpressReconciliationFw newEntity = getEntity(param);
        ToolUtil.copyProperties(newEntity, oldEntity);
        this.updateById(newEntity);
    }

    @Override
    public ToolExpressReconciliationFwResult findBySpec(ToolExpressReconciliationFwParam param){
        return null;
    }

    @Override
    public List<ToolExpressReconciliationFwResult> findListBySpec(ToolExpressReconciliationFwParam param){
        return this.baseMapper.findListBySpec(param);
    }

    @Override
    public LayuiPageInfo findPageBySpec(ToolExpressReconciliationFwParam param,String condition,Long operateId){
        Page pageContext = getPageContext();
        IPage page = this.baseMapper.customPageList(pageContext, param,condition, operateId);
        return LayuiPageFactory.createPageInfo(page);
    }

    @Override
    public LayuiPageInfo findPageBySpecDz(ToolExpressReconciliationFwParam param,String condition){
        Page pageContext = getPageContext();
        IPage page = this.baseMapper.findPageBySpecDz(pageContext, param,condition);
        return LayuiPageFactory.createPageInfo(page);
    }

    private Serializable getKey(ToolExpressReconciliationFwParam param){
        return param.getId();
    }

    private Page getPageContext() {
        return LayuiPageFactory.defaultPage();
    }

    private ToolExpressReconciliationFw getOldEntity(ToolExpressReconciliationFwParam param) {
        return this.getById(getKey(param));
    }

    private ToolExpressReconciliationFw getEntity(ToolExpressReconciliationFwParam param) {
        ToolExpressReconciliationFw entity = new ToolExpressReconciliationFw();
        ToolUtil.copyProperties(param, entity);
        return entity;
    }
    
    @Resource
    private UserService userService;
    
    @Resource
    private ToolExpressReconciliationRfService toolExpressReconciliationRfService;

    @Resource
    private ToolExpressReconciliationYfInstallService toolExpressReconciliationYfInstallService;

    @Resource
    private ToolExpressReconciliationFwInstallService toolExpressReconciliationFwInstallService;
    
	@Override
	public ResponseData getExceldata(MultipartFile file,Long reconciliationId, ResponseData data) throws IOException{
		String fileName = file.getOriginalFilename();
		
		User user = userService.getcurrentUser();

		BigDecimal fu2 = new BigDecimal("-2");
    	BigDecimal as = new BigDecimal("0");
    	BigDecimal qj05 = new BigDecimal("0.5");
    	BigDecimal yi = new BigDecimal("1");
    	BigDecimal er = new BigDecimal("2");
    	BigDecimal san = new BigDecimal("3");
    	BigDecimal wu = new BigDecimal("5");
    	BigDecimal wudwu = new BigDecimal("5.5");
    	BigDecimal shi = new BigDecimal("10");
		
		List<ToolExpressReconciliationFwParam> fwlist = new ArrayList<ToolExpressReconciliationFwParam>();//丰网对账单
		List<ToolExpressReconciliationRfParam> rflist = new ArrayList<ToolExpressReconciliationRfParam>();//软风对账单
        
		ToolExpressReconciliationFwInstallParam toolExpressReconciliationFwInstallParam = new ToolExpressReconciliationFwInstallParam();
		toolExpressReconciliationFwInstallParam.setDeptId(user.getDeptId());
        List<ToolExpressReconciliationFwInstallResult> yzi = toolExpressReconciliationFwInstallService.findListBySpec(toolExpressReconciliationFwInstallParam,user.getOperateId());//丰网报价
        
        ToolExpressReconciliationYfInstallParam expressReconciliationYfInstallParam = new ToolExpressReconciliationYfInstallParam();
        expressReconciliationYfInstallParam.setDeptId(user.getDeptId());
        List<ToolExpressReconciliationYfInstallResult> rfi = toolExpressReconciliationYfInstallService.findListBySpec(expressReconciliationYfInstallParam,user.getOperateId());//软风报价
		
		if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
			data.setMessage("上传文件格式不正确");
			data.setSuccess(false);
			return data;
        }

		boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream is = file.getInputStream();
        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        Sheet sheet = wb.getSheetAt(0);
        
        for (int r = 1; r <= sheet.getLastRowNum()+1; r++) {
        	Row row = sheet.getRow(r);
            if (row == null){
                continue;
            }
            
            ToolExpressReconciliationFwParam fwParam = new ToolExpressReconciliationFwParam();//丰网账单
            fwParam.setReconciliationId(reconciliationId);
            
            ToolExpressReconciliationRfParam rfffParam = new ToolExpressReconciliationRfParam();//软风账单
            rfffParam.setV1("fw");
            rfffParam.setReconciliationId(reconciliationId);

            String  outNumber = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(11));//出库单号
            if (ToolUtil.isNotEmpty(outNumber)) {
            	rfffParam.setOutNumber(outNumber);
			}
            
            String  shopNumber = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(12));//店铺编号
            if (ToolUtil.isNotEmpty(shopNumber)) {
            	rfffParam.setShopNumber(shopNumber);
			}
            
            String  shopName = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(13));//店铺名称
            if (ToolUtil.isNotEmpty(shopName)) {
            	rfffParam.setShopName(shopName);
			}
            
            String  platform = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(14));//平台站点
            if (ToolUtil.isNotEmpty(platform)) {
            	rfffParam.setPlatform(platform);
			}
            
            String  outWNumber = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(15));//出仓单号
            if (ToolUtil.isNotEmpty(outWNumber)) {
            	rfffParam.setOutWNumber(outWNumber);
			}
            
            String  inOrderNumber = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(16));//内部订单号
            if (ToolUtil.isNotEmpty(inOrderNumber)) {
            	rfffParam.setInOrderNumber(inOrderNumber);
			}
            
            String  outTime = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(17));//出库日期
            if (ToolUtil.isNotEmpty(outTime)) {
            	rfffParam.setOutTime(outTime);
			}
            
            String  states = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(18));//状态
            if (ToolUtil.isNotEmpty(states)) {
            	rfffParam.setStates(states);
			}
            
            String  expressName = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(19));//快递公司
            if (ToolUtil.isNotEmpty(expressName)) {
        		rfffParam.setExpressName(expressName);
            }
            
            String  provinces = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(20));//省
            if (ToolUtil.isNotEmpty(provinces)) {
        		rfffParam.setProvinces(provinces);
            }
            
            String  city = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(21));//市
            if (ToolUtil.isNotEmpty(city)) {
        		rfffParam.setCity(city);
    			if(city.equals("深圳市")||city.equals("深圳")) {
    				rfffParam.setRemarks("深圳");
    			}
            }
            
            String  area = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(22));//区
            if (ToolUtil.isNotEmpty(area)) {
        		rfffParam.setArea(area);
    			if(area.equals("虎门镇")||area.equals("虎门港管委会")) {
    				rfffParam.setRemarks("虎门");
    			}
            }
            
            String  street = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(23));//街道
            if (ToolUtil.isNotEmpty(street)) {
        		rfffParam.setStreet(street);
    			if(street.equals("虎门镇")) {
    				rfffParam.setRemarks("虎门");
    			}
            }
            
            String  nums = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(24));//发货件数
            if (ToolUtil.isNotEmpty(nums)) {
        		rfffParam.setNums(nums);
        		fwParam.setQuantity(nums);
            }

            String  orderNumber = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(4));//运单号
            if (ToolUtil.isNotEmpty(orderNumber)) {
            	fwParam.setOrderNumber(orderNumber);
            	rfffParam.setOrderNumber(orderNumber);
			}
            
            String  shop = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(8));//店铺
            if (ToolUtil.isNotEmpty(shop)) {
            	fwParam.setShop(shop);
			}
            
            String  outlet = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(5));//出件网点
            if (ToolUtil.isNotEmpty(outlet)) {
            	fwParam.setOutlet(outlet);
			}
            
            String  ruwangshijian = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(6));//入网时间
            if (ToolUtil.isNotEmpty(ruwangshijian)) {
            	fwParam.setRuwangshijian(ruwangshijian);
			}
            
            String  sProvince = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(7));//收件人省份
            if (ToolUtil.isNotEmpty(sProvince)) {
            	fwParam.setSProvince(sProvince);
            	fwParam.setProvince(sProvince.replaceAll("省", "").replaceAll("市", "").replaceAll("壮族自治区", "").replaceAll("回族自治区", "").replaceAll("维吾尔自治区", "").replaceAll("自治区", ""));//省份
            	rfffParam.setProvince(sProvince.replaceAll("省", "").replaceAll("市", "").replaceAll("壮族自治区", "").replaceAll("回族自治区", "").replaceAll("维吾尔自治区", "").replaceAll("自治区", ""));
			}
            
            String  ycProvince = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(20));//云仓省份
            if (ToolUtil.isNotEmpty(ycProvince)) {
            	fwParam.setYcProvince(ycProvince.replaceAll("省", "").replaceAll("市", "").replaceAll("壮族自治区", "").replaceAll("回族自治区", "").replaceAll("维吾尔自治区", "").replaceAll("自治区", ""));
            	if(ToolUtil.isNotEmpty(fwParam.getProvince())) {
    				if(fwParam.getProvince().equals(fwParam.getYcProvince())) {
    					fwParam.setProvinceTrue("true");
    				} else {
    					fwParam.setProvinceTrue("false");
    				}
    			}
			}
            
            String  settlementWeight = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(2));//结算重量
            if (ToolUtil.isNotEmpty(settlementWeight)) {
    			BigDecimal settlementWeightbig = new BigDecimal(settlementWeight).setScale(2, BigDecimal.ROUND_HALF_UP);
            	fwParam.setSettlementWeight(settlementWeightbig.toString());

            	rfffParam.setChargingWeightKg(settlementWeightbig.toString());
            	rfffParam.setRoundingWeightKg(settlementWeightbig.setScale( 0, BigDecimal.ROUND_UP ).longValue()+"");//取整重量
			}
            
            String  settlementExpenses = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(0));//结算费用
            if (ToolUtil.isNotEmpty(settlementExpenses)) {
    			BigDecimal settlementExpensesbig = new BigDecimal(settlementExpenses).setScale(2, BigDecimal.ROUND_HALF_UP);
            	fwParam.setSettlementExpenses(settlementExpensesbig.toString());
			}
            
            String  additional = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(1));//疫情加收
            if (ToolUtil.isNotEmpty(additional)) {
    			BigDecimal additionalbig = new BigDecimal(additional).setScale(2, BigDecimal.ROUND_HALF_UP);
            	fwParam.setAdditional(additionalbig.toString());
            	fwParam.setAdditional2(additionalbig.toString());
			}
            
            String  settlementExpensesSum = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(3));//合计结算费用
            if (ToolUtil.isNotEmpty(settlementExpensesSum)) {
    			BigDecimal settlementExpensesSumbig = new BigDecimal(settlementExpensesSum).setScale(2, BigDecimal.ROUND_HALF_UP);
            	fwParam.setSettlementExpensesSum(settlementExpensesSumbig.toString());
			} else {
				break;
			}
            
            String  ycWeight = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(9));//云仓重量
            if (ToolUtil.isNotEmpty(ycWeight)) {
    			BigDecimal ycWeightbig = new BigDecimal(ycWeight).setScale(3, BigDecimal.ROUND_HALF_UP);
    			fwParam.setYcWeight(ycWeightbig.toString());
    			
    			if(ToolUtil.isNotEmpty(fwParam.getSettlementWeight())) {
    				BigDecimal settlementWeightbig = new BigDecimal(fwParam.getSettlementWeight()).setScale(2,BigDecimal.ROUND_HALF_UP);
    				if(ycWeightbig.compareTo(settlementWeightbig) == 1) {
    					rfffParam.setChargingWeightKg(ycWeightbig.toString());
    					rfffParam.setRoundingWeightKg(ycWeightbig.setScale( 0, BigDecimal.ROUND_UP ).longValue()+"");//取整重量
    				}
                	
    				BigDecimal weightChayi = settlementWeightbig.subtract(ycWeightbig).setScale(3,BigDecimal.ROUND_HALF_UP);
    				fwParam.setWeightChayi(weightChayi.toString());//重量差异
    			}
            }
        	
            if (ToolUtil.isNotEmpty(fwParam.getSettlementWeight())) {
            	BigDecimal settlementWeightbig = new BigDecimal(fwParam.getSettlementWeight()).setScale(2, BigDecimal.ROUND_HALF_UP);//结算重量
            	if(ToolUtil.isNotEmpty(fwParam.getProvince())) {
            		//匹配丰网报价   计算核对邮费  计算邮费差异
            		for (ToolExpressReconciliationFwInstallResult i : yzi) {
						if(i.getProvince().equals(fwParam.getProvince())) {
        					if(settlementWeightbig.compareTo(as) == 1 && settlementWeightbig.compareTo(qj05) != 1) {//0 -  500g
        						if(ToolUtil.isNotEmpty(i.getQj05g())) {
        							fwParam.setWeightQj("0-0.5kg");
        							fwParam.setPostageCheck(i.getQj05g());
        							BigDecimal postageCheckSum = new BigDecimal("0");//核对总快递费 =   核对快递费 + 北京、上海加收 + 疫情加收
        							BigDecimal postageCheck = new BigDecimal(i.getQj05g());
        							postageCheckSum = postageCheckSum.add(postageCheck).setScale(2, BigDecimal.ROUND_HALF_UP);//+核对快递费
        							if(ToolUtil.isNotEmpty(i.getRemarks())) {
        								postageCheckSum = postageCheckSum.add(yi).setScale(2, BigDecimal.ROUND_HALF_UP);//+ 北京、上海加收
        								fwParam.setAdditionalBjsh("1");
        							}
        							if(ToolUtil.isNotEmpty(fwParam.getAdditional2())) {
            							BigDecimal additional2 = new BigDecimal(fwParam.getAdditional2());
        								postageCheckSum = postageCheckSum.add(additional2).setScale(2, BigDecimal.ROUND_HALF_UP);// + 疫情加收
        							}
        							fwParam.setPostageCheckSum(postageCheckSum.toString());
        							
        							if(ToolUtil.isNotEmpty(fwParam.getSettlementExpensesSum())) {//运费差异 = 合计结算费用  - 核对总快递费
        								BigDecimal settlementExpensesSumbig = new BigDecimal(fwParam.getSettlementExpensesSum());
        								BigDecimal postageDifference = settlementExpensesSumbig.subtract(postageCheckSum).setScale(2, BigDecimal.ROUND_HALF_UP);
        								fwParam.setPostageDifference(postageDifference.toString());
        							}
        						}
        						break;
        					}
        					if(settlementWeightbig.compareTo(qj05) == 1 && settlementWeightbig.compareTo(yi) != 1) {//500-1kg
        						if(ToolUtil.isNotEmpty(i.getQj51kg())) {
        							fwParam.setWeightQj("0.51-1kg");
        							fwParam.setPostageCheck(i.getQj51kg());
        							BigDecimal postageCheckSum = new BigDecimal("0");//核对总快递费 =   核对快递费 + 北京、上海加收 + 疫情加收
        							BigDecimal postageCheck = new BigDecimal(i.getQj51kg());
        							postageCheckSum = postageCheckSum.add(postageCheck).setScale(2, BigDecimal.ROUND_HALF_UP);//+核对快递费
        							if(ToolUtil.isNotEmpty(i.getRemarks())) {
        								postageCheckSum = postageCheckSum.add(yi).setScale(2, BigDecimal.ROUND_HALF_UP);//+ 北京、上海加收
        								fwParam.setAdditionalBjsh("1");
        							}
        							if(ToolUtil.isNotEmpty(fwParam.getAdditional2())) {
            							BigDecimal additional2 = new BigDecimal(fwParam.getAdditional2());
        								postageCheckSum = postageCheckSum.add(additional2).setScale(2, BigDecimal.ROUND_HALF_UP);// + 疫情加收
        							}
        							fwParam.setPostageCheckSum(postageCheckSum.toString());
        							
        							if(ToolUtil.isNotEmpty(fwParam.getSettlementExpensesSum())) {//运费差异 = 合计结算费用  - 核对总快递费
        								BigDecimal settlementExpensesSumbig = new BigDecimal(fwParam.getSettlementExpensesSum());
        								BigDecimal postageDifference = settlementExpensesSumbig.subtract(postageCheckSum).setScale(2, BigDecimal.ROUND_HALF_UP);
        								fwParam.setPostageDifference(postageDifference.toString());
        							}
        						}
        						break;
        					}
        					if(settlementWeightbig.compareTo(yi) == 1 && settlementWeightbig.compareTo(er) != 1) {//1-2kg
        						if(ToolUtil.isNotEmpty(i.getQj12kg())) {
        							fwParam.setWeightQj("1.01-2kg");
        							fwParam.setPostageCheck(i.getQj12kg());
        							BigDecimal postageCheckSum = new BigDecimal("0");//核对总快递费 =   核对快递费 + 北京、上海加收 + 疫情加收
        							BigDecimal postageCheck = new BigDecimal(i.getQj12kg());
        							postageCheckSum = postageCheckSum.add(postageCheck).setScale(2, BigDecimal.ROUND_HALF_UP);//+核对快递费
        							if(ToolUtil.isNotEmpty(i.getRemarks())) {
        								postageCheckSum = postageCheckSum.add(yi).setScale(2, BigDecimal.ROUND_HALF_UP);//+ 北京、上海加收
        								fwParam.setAdditionalBjsh("1");
        							}
        							if(ToolUtil.isNotEmpty(fwParam.getAdditional2())) {
            							BigDecimal additional2 = new BigDecimal(fwParam.getAdditional2());
        								postageCheckSum = postageCheckSum.add(additional2).setScale(2, BigDecimal.ROUND_HALF_UP);// + 疫情加收
        							}
        							fwParam.setPostageCheckSum(postageCheckSum.toString());
        							
        							if(ToolUtil.isNotEmpty(fwParam.getSettlementExpensesSum())) {//运费差异 = 合计结算费用  - 核对总快递费
        								BigDecimal settlementExpensesSumbig = new BigDecimal(fwParam.getSettlementExpensesSum());
        								BigDecimal postageDifference = settlementExpensesSumbig.subtract(postageCheckSum).setScale(2, BigDecimal.ROUND_HALF_UP);
        								fwParam.setPostageDifference(postageDifference.toString());
        							}
        						}
        						break;
        					}
        					if(settlementWeightbig.compareTo(er) == 1 && settlementWeightbig.compareTo(san) != 1) {//2-3kg
        						if(ToolUtil.isNotEmpty(i.getQj23kg())) {
        							fwParam.setWeightQj("2.01-3kg");
        							fwParam.setPostageCheck(i.getQj23kg());
        							BigDecimal postageCheckSum = new BigDecimal("0");//核对总快递费 =   核对快递费 + 北京、上海加收 + 疫情加收
        							BigDecimal postageCheck = new BigDecimal(i.getQj23kg());
        							postageCheckSum = postageCheckSum.add(postageCheck).setScale(2, BigDecimal.ROUND_HALF_UP);//+核对快递费
        							if(ToolUtil.isNotEmpty(i.getRemarks())) {
        								postageCheckSum = postageCheckSum.add(yi).setScale(2, BigDecimal.ROUND_HALF_UP);//+ 北京、上海加收
        								fwParam.setAdditionalBjsh("1");
        							}
        							if(ToolUtil.isNotEmpty(fwParam.getAdditional2())) {
            							BigDecimal additional2 = new BigDecimal(fwParam.getAdditional2());
        								postageCheckSum = postageCheckSum.add(additional2).setScale(2, BigDecimal.ROUND_HALF_UP);// + 疫情加收
        							}
        							fwParam.setPostageCheckSum(postageCheckSum.toString());
        							
        							if(ToolUtil.isNotEmpty(fwParam.getSettlementExpensesSum())) {//运费差异 = 合计结算费用  - 核对总快递费
        								BigDecimal settlementExpensesSumbig = new BigDecimal(fwParam.getSettlementExpensesSum());
        								BigDecimal postageDifference = settlementExpensesSumbig.subtract(postageCheckSum).setScale(2, BigDecimal.ROUND_HALF_UP);
        								fwParam.setPostageDifference(postageDifference.toString());
        							}
        						}
        						break;
        					}
        					if(settlementWeightbig.compareTo(san) == 1 && settlementWeightbig.compareTo(wu) != 1) {//3-5kg
        						if(ToolUtil.isNotEmpty(i.getFirstWeight35kg()) && ToolUtil.isNotEmpty(i.getCWeight35kg())) {// 核对快递费 = 首重+（续重*重量）
        							fwParam.setWeightQj("3kg以上");
        							fwParam.setFirstWeight(i.getFirstWeight35kg());
        							fwParam.setWeightContinuation(i.getCWeight35kg());

    								BigDecimal firstWeight = new BigDecimal(i.getFirstWeight35kg());//首重
    								BigDecimal weightContinuation = new BigDecimal(i.getCWeight35kg());//续重

    								BigDecimal xmulz = weightContinuation.multiply(settlementWeightbig).setScale(2, BigDecimal.ROUND_HALF_UP);//续重 *  重量
    								BigDecimal addxmulz = firstWeight.add(xmulz).setScale(2, BigDecimal.ROUND_HALF_UP);//首重+（续重*重量）
        							fwParam.setPostageCheck(addxmulz.toString());
        							
        							BigDecimal postageCheckSum = new BigDecimal("0");//核对总快递费 =   核对快递费 + 北京、上海加收 + 疫情加收
        							postageCheckSum = postageCheckSum.add(addxmulz).setScale(2, BigDecimal.ROUND_HALF_UP);//+核对快递费
        							if(ToolUtil.isNotEmpty(i.getRemarks())) {
        								postageCheckSum = postageCheckSum.add(yi).setScale(2, BigDecimal.ROUND_HALF_UP);//+ 北京、上海加收
        								fwParam.setAdditionalBjsh("1");
        							}
        							if(ToolUtil.isNotEmpty(fwParam.getAdditional2())) {
            							BigDecimal additional2 = new BigDecimal(fwParam.getAdditional2());
        								postageCheckSum = postageCheckSum.add(additional2).setScale(2, BigDecimal.ROUND_HALF_UP);// + 疫情加收
        							}
        							fwParam.setPostageCheckSum(postageCheckSum.toString());
        							
        							if(ToolUtil.isNotEmpty(fwParam.getSettlementExpensesSum())) {//运费差异 = 合计结算费用  - 核对总快递费
        								BigDecimal settlementExpensesSumbig = new BigDecimal(fwParam.getSettlementExpensesSum());
        								BigDecimal postageDifference = settlementExpensesSumbig.subtract(postageCheckSum).setScale(2, BigDecimal.ROUND_HALF_UP);
        								fwParam.setPostageDifference(postageDifference.toString());
        							}
        						}
        						break;
        					}
        					if(settlementWeightbig.compareTo(wu) == 1) {//5kg以上
        						if(ToolUtil.isNotEmpty(i.getFirstWeight5kg())) {// 核对快递费 = 首重+（续重*重量）
    								BigDecimal firstWeight = new BigDecimal(i.getFirstWeight5kg());//首重
    								BigDecimal weightContinuation = new BigDecimal("0");//续重
        							if(settlementWeightbig.compareTo(shi) != 1) {
        								if(ToolUtil.isNotEmpty(i.getCWeight510kg())) {//5-10KG续重价格
        									weightContinuation = new BigDecimal(i.getCWeight510kg());
                							fwParam.setWeightContinuation(i.getCWeight510kg());
        								} else {
        									break;
        								}
        							} else {
        								if(ToolUtil.isNotEmpty(i.getCWeight10kg())) {//10KG以上续重价格
        									weightContinuation = new BigDecimal(i.getCWeight10kg());
                							fwParam.setWeightContinuation(i.getCWeight10kg());
        								} else {
        									break;
        								}
        							}
        							fwParam.setWeightQj("3kg以上");
        							fwParam.setFirstWeight(i.getFirstWeight5kg());

    								BigDecimal xmulz = weightContinuation.multiply(settlementWeightbig).setScale(2, BigDecimal.ROUND_HALF_UP);//续重 *  重量
    								BigDecimal addxmulz = firstWeight.add(xmulz).setScale(2, BigDecimal.ROUND_HALF_UP);//首重+（续重*重量）
        							fwParam.setPostageCheck(addxmulz.toString());
        							
        							BigDecimal postageCheckSum = new BigDecimal("0");//核对总快递费 =   核对快递费 + 北京、上海加收 + 疫情加收
        							postageCheckSum = postageCheckSum.add(addxmulz).setScale(2, BigDecimal.ROUND_HALF_UP);//+核对快递费
        							if(ToolUtil.isNotEmpty(i.getRemarks())) {
        								postageCheckSum = postageCheckSum.add(yi).setScale(2, BigDecimal.ROUND_HALF_UP);//+ 北京、上海加收
        								fwParam.setAdditionalBjsh("1");
        							}
        							if(ToolUtil.isNotEmpty(fwParam.getAdditional2())) {
            							BigDecimal additional2 = new BigDecimal(fwParam.getAdditional2());
        								postageCheckSum = postageCheckSum.add(additional2).setScale(2, BigDecimal.ROUND_HALF_UP);// + 疫情加收
        							}
        							fwParam.setPostageCheckSum(postageCheckSum.toString());
        							
        							if(ToolUtil.isNotEmpty(fwParam.getSettlementExpensesSum())) {//运费差异 = 合计结算费用  - 核对总快递费
        								BigDecimal settlementExpensesSumbig = new BigDecimal(fwParam.getSettlementExpensesSum());
        								BigDecimal postageDifference = settlementExpensesSumbig.subtract(postageCheckSum).setScale(2, BigDecimal.ROUND_HALF_UP);
        								fwParam.setPostageDifference(postageDifference.toString());
        							}
        						}
        						break;
        					}
						}
					}
            	}
			}

            if(ToolUtil.isNotEmpty(rfffParam.getChargingWeightKg())) {
            	BigDecimal chargingWeightKgbig = new BigDecimal(rfffParam.getChargingWeightKg()).setScale(3, BigDecimal.ROUND_HALF_UP);
                if(ToolUtil.isNotEmpty(rfffParam.getProvince())) {
            		//匹配软风报价   计算快递费，邮政账单收入、利润
            		BigDecimal sumExpressMoney = new BigDecimal("0");//总快递费
            		String provincestr = "";
            		if(ToolUtil.isNotEmpty(rfffParam.getRemarks())) {
            			provincestr = rfffParam.getRemarks();
            		} else {
            			provincestr = rfffParam.getProvince();
            		}
            		for (ToolExpressReconciliationYfInstallResult i : rfi) {
        				if(provincestr.equals(i.getDestination())) {
        					if(provincestr.equals("海南")||provincestr.equals("新疆")||provincestr.equals("西藏")) {
            					if(chargingWeightKgbig.compareTo(as) == 1 && chargingWeightKgbig.compareTo(yi) != 1) {//0 - 1kg
            						fwParam.setWeightQj2("1KG以下");
            					}
            					if(chargingWeightKgbig.compareTo(yi) == 1 && chargingWeightKgbig.compareTo(er) != 1) {//1 - 2kg
            						fwParam.setWeightQj2("1-2KG");
            					}
            					if(chargingWeightKgbig.compareTo(er) == 1 && chargingWeightKgbig.compareTo(san) != 1) {//2 - 3kg
            						fwParam.setWeightQj2("2-3KG");
            					}
            					if(chargingWeightKgbig.compareTo(san) == 1) {//3kg以上
            						fwParam.setWeightQj2("3KG以上");
            					}
        						if(ToolUtil.isNotEmpty(i.getUnilateralism()) && ToolUtil.isNotEmpty(i.getCWeight())) {
        							BigDecimal unilateralism = new BigDecimal(i.getUnilateralism());//面单费
        							BigDecimal cCWeight = new BigDecimal(i.getCWeight());//续重
        							BigDecimal wmulcCWeight = chargingWeightKgbig.multiply(cCWeight);//重量*续重
        							BigDecimal expressMoney = unilateralism.add(wmulcCWeight).setScale(3, BigDecimal.ROUND_HALF_UP);//面单费+（重量*续重）
        							sumExpressMoney = expressMoney;
        							rfffParam.setUnilateralism(unilateralism.toString());
        							rfffParam.setCWeight(cCWeight.toString());
        							rfffParam.setExpressMoney(expressMoney.toString());//快递费
        						}
        					} else {
            					if(chargingWeightKgbig.compareTo(as) == 1 && chargingWeightKgbig.compareTo(yi) != 1) {//0 - 1kg
            						fwParam.setWeightQj2("1KG以下");
            						if(ToolUtil.isNotEmpty(i.getQj01kg())) {
        								rfffParam.setExpressMoney(i.getQj01kg());//快递费
        								BigDecimal qj01kg = new BigDecimal(i.getQj01kg());
        								sumExpressMoney = qj01kg;
            						}
            					}
            					if(chargingWeightKgbig.compareTo(yi) == 1 && chargingWeightKgbig.compareTo(er) != 1) {//1 - 2kg
            						fwParam.setWeightQj2("1-2KG");
            						if(ToolUtil.isNotEmpty(i.getQj12kg())) {
        								rfffParam.setExpressMoney(i.getQj12kg());//快递费
        								BigDecimal qj12kg = new BigDecimal(i.getQj12kg());
        								sumExpressMoney = qj12kg;
            						}
            					}
            					if(chargingWeightKgbig.compareTo(er) == 1 && chargingWeightKgbig.compareTo(san) != 1) {//2 - 3kg
            						fwParam.setWeightQj2("2-3KG");
            						if(ToolUtil.isNotEmpty(i.getQj23kg())) {
        								rfffParam.setExpressMoney(i.getQj23kg());//快递费
        								BigDecimal qj23kg = new BigDecimal(i.getQj23kg());
        								sumExpressMoney = qj23kg;
            						}
            					}
            					if(chargingWeightKgbig.compareTo(san) == 1) {//3kg以上
            						fwParam.setWeightQj2("3KG以上");
        							if(ToolUtil.isNotEmpty(i.getUnilateralism()) && ToolUtil.isNotEmpty(i.getCWeight())) {
        								BigDecimal unilateralism = new BigDecimal(i.getUnilateralism());//面单费
        								BigDecimal cCWeight = new BigDecimal(i.getCWeight());//续重
        								BigDecimal wmulcCWeight = chargingWeightKgbig.multiply(cCWeight);//重量*续重
        								BigDecimal expressMoney = unilateralism.add(wmulcCWeight).setScale(3, BigDecimal.ROUND_HALF_UP);//面单费+（重量*续重）
        								sumExpressMoney = expressMoney;
        								rfffParam.setUnilateralism(unilateralism.toString());
        								rfffParam.setCWeight(cCWeight.toString());
        								rfffParam.setExpressMoney(expressMoney.toString());//快递费
        							}
            					}
        					}
        					if(ToolUtil.isNotEmpty(i.getAdditional())) {//地区加收
        						BigDecimal additional2 = new BigDecimal(i.getAdditional());
        						BigDecimal wmuladditional = chargingWeightKgbig.multiply(additional2).setScale(4, BigDecimal.ROUND_HALF_UP);//重量*地区加收
        						rfffParam.setAdditional(wmuladditional.toString());
        						sumExpressMoney = sumExpressMoney.add(wmuladditional);
        					}
        					if(ToolUtil.isNotEmpty(i.getStandard()) && ToolUtil.isNotEmpty(i.getMinimum())) {//中转费
        						rfffParam.setStandard(i.getStandard());
        						rfffParam.setMinimum(i.getMinimum());
        						BigDecimal standard = new BigDecimal(i.getStandard());//二三级中转标准
        						BigDecimal minimum = new BigDecimal(i.getMinimum());//二三级中转最低
        						BigDecimal wmulstandard = standard.multiply(chargingWeightKgbig).setScale(4, BigDecimal.ROUND_HALF_UP);//二三级中转标准*重量
        						//如果  二三级中转标准*重量 > 二三级中转最低 就用二三级中转标准*重量 作为中转费，不然就用二三级中转最低作为中转费
        						if(wmulstandard.compareTo(minimum) == 1) {
        							rfffParam.setTransferMoney(wmulstandard.toString());
        							sumExpressMoney = sumExpressMoney.add(wmulstandard);
        						} else {
        							rfffParam.setTransferMoney(i.getMinimum());
        							sumExpressMoney = sumExpressMoney.add(minimum);
        						}
        					}
        					if(chargingWeightKgbig.compareTo(wudwu) == 1) {//5.5kg以上
        						if(ToolUtil.isNotEmpty(i.getZz55above())) {//5.5Kg以上的中转费
        							BigDecimal zz55above = new BigDecimal(i.getZz55above());
        							BigDecimal wmulzz55above = zz55above.multiply(chargingWeightKgbig).setScale(4, BigDecimal.ROUND_HALF_UP);
        							rfffParam.setTransferMoney(wmulzz55above.toString());
        							sumExpressMoney = sumExpressMoney.add(wmulzz55above);
        						}
        					}
        					rfffParam.setSumExpressMoney(sumExpressMoney.setScale(2, BigDecimal.ROUND_HALF_UP).toString());//总快递费
        					fwParam.setIncome(sumExpressMoney.setScale(2, BigDecimal.ROUND_HALF_UP).toString());//收入
        					break;
        				}
        			}
                }
            }
            
            if(ToolUtil.isNotEmpty(fwParam.getSettlementExpensesSum()) && ToolUtil.isNotEmpty(rfffParam.getSumExpressMoney())) {
            	BigDecimal settlementExpensesSumbig = new BigDecimal(fwParam.getSettlementExpensesSum());//合计结算费用
            	BigDecimal sumExpressMoney = new BigDecimal(rfffParam.getSumExpressMoney());//总快递费
            	BigDecimal profit = sumExpressMoney.subtract(settlementExpensesSumbig).setScale(2,BigDecimal.ROUND_HALF_UP);//总快递费  -  合计结算费用 = 利润
            	fwParam.setProfit(profit.toString());//利润
            }
            
            String  customerName = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(10));//客户
            if (ToolUtil.isNotEmpty(customerName)) {
            	fwParam.setCustomerName(customerName);
			}
            
            fwParam.setAdvanceCharge("-2");//预付款
            
            if(ToolUtil.isNotEmpty(fwParam.getSettlementExpensesSum())) {//应付  =  合计结算费用 + 预付款
				BigDecimal settlementExpensesSumbig = new BigDecimal(fwParam.getSettlementExpensesSum());
				BigDecimal copeWithbig = settlementExpensesSumbig.add(fu2).setScale(2, BigDecimal.ROUND_HALF_UP);
				fwParam.setCopeWith(copeWithbig.toString());
            }
            
            fwlist.add(fwParam);
            
            if(ToolUtil.isNotEmpty(customerName)) {
            	if(customerName.equals("软风2C")) {
            		rflist.add(rfffParam);
            	}
            }
        }
        if(fwlist.size() > 0) {
        	this.saveBatchList(fwlist);
        }
        if(rflist.size() > 0) {
        	toolExpressReconciliationRfService.saveBatchList(rflist);
        }
		return ResponseData.success();
	}

	@Override
	public Workbook exportToExcel(Long reconciliationId) {
	    //这是表头
	    String[] arr = {"运单号","店铺","出件网点","入网时间","省份"," 收件人省份","云仓省份","1","结算重量","结算费用","疫情加收","合计结算费用"
	    		,"云仓重量","重量差异","重量区间","核对快递费","首重","续重","北京、上海加收","疫情加收","核对总快递费","运费差异"
	    		,"客户","收入","利润","重量区间","数量","预付款","应付","备注"};
	    //这是具体数据
	    List<List<String>> list = new ArrayList<>();
	    
	    ToolExpressReconciliationFwParam param = new ToolExpressReconciliationFwParam();
	    param.setReconciliationId(reconciliationId);
	    List<ToolExpressReconciliationFwResult> datalist = this.findListBySpec(param);
	    for (ToolExpressReconciliationFwResult r : datalist) {
		    List<String> list1 = new ArrayList<String>();
		    list1.add(r.getOrderNumber());
		    list1.add(r.getShop());
		    list1.add(r.getOutlet());
		    list1.add(r.getRuwangshijian());
		    list1.add(r.getProvince());
		    list1.add(r.getSProvince());
		    list1.add(r.getYcProvince());
		    list1.add(r.getProvinceTrue());
		    list1.add(r.getSettlementWeight());
		    list1.add(r.getSettlementExpenses());
		    list1.add(r.getAdditional());
		    list1.add(r.getSettlementExpensesSum());
		    list1.add(r.getYcWeight());
		    list1.add(r.getWeightChayi());
		    list1.add(r.getWeightQj());
		    list1.add(r.getPostageCheck());
		    list1.add(r.getFirstWeight());
		    list1.add(r.getWeightContinuation());
		    list1.add(r.getAdditionalBjsh());
		    list1.add(r.getAdditional2());
		    list1.add(r.getPostageCheckSum());
		    list1.add(r.getPostageDifference());
		    list1.add(r.getCustomerName());
		    list1.add(r.getIncome());
		    list1.add(r.getProfit());
		    list1.add(r.getWeightQj2());
		    list1.add(r.getQuantity());
		    list1.add(r.getAdvanceCharge());
		    list1.add(r.getCopeWith());
		    list1.add(r.getRemarks());
		    list.add(list1);
		}
	    return ExcelWrite.writeToExcelByList(arr, list);
	}

	@Override
	public void deleteBySpec(ToolExpressReconciliationFwParam param) {
		this.baseMapper.deleteBySpec(param);
	}

	@Override
	public boolean saveBatchList(List<ToolExpressReconciliationFwParam> list) {
		return this.baseMapper.saveBatchList(list);
	}

	@Override
	public List<orderCount> findorderCount(ToolExpressReconciliationFwParam param, Long operateId) {
		return this.baseMapper.findorderCount(param,operateId);
	}
}
